using Microsoft.AspNetCore.Cors;
using Microsoft.AspNetCore.Mvc;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using ClosedXML.Excel;
using mapshiftApplication.Helpers;
using mapshiftApplication.Services;

namespace mapshiftApplication.Controllers
{
    [ApiController]
    [EnableCors("AllowFrontend")]
    [Route("api/mapshift")]
    public class MapshiftController : ControllerBase
    {
        private readonly IOracleConnectionFactory _oracleConnectionFactory;
        
        private readonly MapShiftService _exportService;

        public MapshiftController(IOracleConnectionFactory oracleConnectionFactory,MapShiftService exportService)
        {
            _oracleConnectionFactory = oracleConnectionFactory;
            _exportService = exportService;
        }

        [HttpPost]
        [Route("add")]
        public async Task<IActionResult> AddRecord([FromBody] MmsRecord record)
        {
            await using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            
            string newMmsId = Guid.NewGuid().ToString();

            await using var command = new OracleCommand(
                "INSERT INTO MMS_MAPSHIFT  (MMS_ID,CUST, DEVICE, CP_NO, OS_BIN, MS_POS, MS_SLIDE, MS_CUSTOM_POS,MS_CUSTOM_BIN,FAIL_YIELD, CREATE_DATE,MODIFY_DATE) VALUES (:mms_id,:cust, :device, :cp_no, :os_bin, :ms_pos, :ms_slide,:ms_custom_pos,:ms_custom_bin, :fail_yield, SYSDATE,SYSDATE)",
                oracleConnection);
            command.Parameters.Add(new OracleParameter("mms_id", newMmsId));
            command.Parameters.Add(new OracleParameter("cust", record.Cust));
            command.Parameters.Add(new OracleParameter("device", record.Device));
            command.Parameters.Add(new OracleParameter("cp_no", record.CpNo));
            command.Parameters.Add(new OracleParameter("os_bin", record.OsBin));
            command.Parameters.Add(new OracleParameter("ms_pos", record.MsPos ?? (object)DBNull.Value));
            command.Parameters.Add(new OracleParameter("ms_slide", record.MsSlide ?? (object)DBNull.Value));
            command.Parameters.Add(new OracleParameter("ms_custom_pos", record.MsCustomPos ?? (object)DBNull.Value));
            command.Parameters.Add(new OracleParameter("ms_custom_bin", record.MsCustomBin ?? (object)DBNull.Value));
            command.Parameters.Add(new OracleParameter("fail_yield", record.FailYield));

            int rows = await command.ExecuteNonQueryAsync();
            return rows > 0 ? Ok(new { message = "Record added successfully",code = "200" }) : BadRequest("Insert failed");
        }
        
        
        
        [HttpGet]
        [Route("query")]
        public async Task<IActionResult> GetRecords([FromQuery] int page = 1, [FromQuery] int pageSize = 10, [FromQuery] string cust = "", [FromQuery] string device = "")
        {
            await using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            string sql = "SELECT * FROM mms_mapshift ";
            
            string countSql = "SELECT COUNT(*) FROM mms_mapshift WHERE 1=1";
            if (!string.IsNullOrEmpty(cust)) countSql += " AND cust = :cust";
            if (!string.IsNullOrEmpty(device)) countSql += " AND device = :device";
            using var countCommand = new OracleCommand(countSql, oracleConnection);
            if (!string.IsNullOrEmpty(cust)) countCommand.Parameters.Add(new OracleParameter("cust", cust));
            if (!string.IsNullOrEmpty(device)) countCommand.Parameters.Add(new OracleParameter("device", device));

            int totalRecords = Convert.ToInt32(await countCommand.ExecuteScalarAsync());
            
            List<OracleParameter> parameters = new List<OracleParameter>();
            List<string> conditions = new List<string>();
            if (!string.IsNullOrEmpty(cust))
            {
                conditions.Add("cust = :cust");
                parameters.Add(new OracleParameter("cust", cust));
            }
            
            if (!string.IsNullOrEmpty(device))
            {
                conditions.Add("device = :device");
                parameters.Add(new OracleParameter("device", device));
            }
            
            if (conditions.Count > 0)
            {
                sql += " WHERE " + string.Join(" AND ", conditions);
            }
            sql += " OFFSET :offset ROWS FETCH NEXT :pageSize ROWS ONLY";
            parameters.Add(new OracleParameter("offset", (page - 1) * pageSize));
            parameters.Add(new OracleParameter("pageSize", pageSize));

            await using var command = new OracleCommand(sql, oracleConnection);
            command.Parameters.AddRange(parameters.ToArray());

            await using var reader = await command.ExecuteReaderAsync();
            var records = new List<MmsRecord>();
            while (await reader.ReadAsync())
            {
                records.Add(new MmsRecord
                {
                    MmsId = reader["mms_id"].ToString()!,
                    Cust = reader["cust"].ToString()!,
                    Device = reader["device"].ToString()!,
                    CpNo = reader["cp_no"].ToString()!,
                    OsBin = reader["os_bin"].ToString()!,
                    MsPos = reader["ms_pos"].ToString()!,
                    MsSlide = reader["ms_slide"].ToString()!,
                    MsCustomPos = reader["ms_custom_pos"].ToString()!,
                    MsCustomBin = reader["ms_custom_bin"].ToString()!,
                    FailYield = reader["fail_yield"].ToString()!,
                });
            }
            return Ok(new { items = records, total = totalRecords });
        }
        
        
        [HttpGet]
        [Route("delete/{id}")]
        public async Task<IActionResult> DeleteRecord(string id)
        {
            await using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            string sql = "DELETE FROM mms_mapshift WHERE mms_id = :id";

            await using var command = new OracleCommand(sql, oracleConnection);
            command.Parameters.Add(new OracleParameter("id", id));

            int rowsAffected = await command.ExecuteNonQueryAsync();
        
            return rowsAffected > 0 ? Ok("Record deleted successfully.") : NotFound("Record not found.");
        }
        
        [HttpPost]
        [Route("edit")]
        public async Task<IActionResult> EditRecord( [FromBody] MmsRecord record)
        {
            await using var oracleConnection = _oracleConnectionFactory.GetConnection("DefaultConnection");
            await oracleConnection.OpenAsync();
            
            string sql = $@"
                UPDATE mms_mapshift 
                SET cust = '{record.Cust}', 
                    device = '{record.Device}', 
                    cp_no = '{record.CpNo}', 
                    os_bin = '{record.OsBin}', 
                    ms_pos = '{record.MsPos}', 
                    ms_slide = '{record.MsSlide}', 
                    ms_custom_pos = '{record.MsCustomPos}',
                    ms_custom_bin = '{record.MsCustomBin}',
                    fail_yield = '{record.FailYield}' 
                WHERE mms_id = '{record.MmsId}'";

            LogHelper.Info("Final Executing SQL: " + sql);


            await using var command = new OracleCommand(sql, oracleConnection);
            int rows = await command.ExecuteNonQueryAsync();
                
            return rows > 0 ? Ok("Record update successfully.") : NotFound("Record not found.");
        }


        [HttpGet]
        [Route("export")]
        public async Task<IActionResult> ExportMapshift()
        {
            var fileBytes = await _exportService.ExportToExcelAsync();
            var fileName = $"MMS_MAPSHIFT_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx";

            return File(fileBytes, 
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", 
                fileName);
            
        }    
        
        
    }

    public class MmsRecord
    {
        public string? MmsId { get; set; }
        public required string Cust { get; set; }
        public required string Device { get; set; }
        public required string CpNo { get; set; }
        public required string OsBin { get; set; }
        public string? MsPos { get; set; }
        public string? MsSlide { get; set; }
        
        public string? MsCustomPos { get; set; }
        
        public string? MsCustomBin { get; set; }
        public required string FailYield { get; set; }
    }
}
